﻿Imports MySql.Data.MySqlClient

Public Class addgroup2_1


    Public SQL As MySqlConnection
    Public cour_id As String
    Public key As String

    Public Sub New(ByRef SQLCon As MySqlConnection)
        InitializeComponent()
        SQL = SQLCon
    End Sub

    Private Sub addgroup2_1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim maxyear As String
        Dim minyear As String
        Dim max() As String
        Dim min() As String
        Dim maxyearint As Integer
        Dim minyearint As Integer

        ComboBox1.Items.Clear()

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM course  order by course_start_date DESC limit 1;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())

            maxyear = mySqlReader("course_start_date")
            max = maxyear.Split("/")
            maxyearint = max(2)

        End While

        SQL.Close()

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM course  order by course_start_date ASC limit 1;"
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader


        While (mySqlReader.Read())

            minyear = mySqlReader("course_start_date")
            min = minyear.Split("/")
            minyearint = min(2)
        End While


        For i As Integer = minyearint To maxyearint Step 1
            If i <> Date.Now.Year And i <> 0 Then
                ComboBox1.Items.Add(i)
            End If
        Next i

        ComboBox1.Items.Add("ALL")

        SQL.Close()


        ComboBox1.Items.Add(Date.Now.Year + 543)
        ComboBox1.Text = Date.Now.Year.ToString + 543

        Showdata()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If cour_id = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else

            Dim NextForm As addgroup2_2 = New addgroup2_2(SQL, cour_id)
            NextForm.Show()
            Me.Close()
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim NextForm As main = New main()
        NextForm.Show()
        Me.Close()
    End Sub

    Private Sub Showdata()
        Dim keydate As String
        '---------------------------------------------Check Start date couse---------------------------
        keydate = ComboBox1.SelectedItem.ToString

        If keydate = "ALL" Then
            keydate = ""

        Else

            keydate = keydate - 543
        End If

        '---------------------------------------------End Check Start date couse---------------------------

        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If

        key = textsearch.Text

        Dim mysqlda As New MySql.Data.MySqlClient.MySqlDataAdapter
        Dim mysqldr As MySql.Data.MySqlClient.MySqlDataReader

        ' Dim comsql As String = "SELECT * FROM Course WHERE (course_id like" + "'%" + key + "%' or course_name like" + "'%" + key + "%'or course_trainer like '%" + key + "%'and course_start_date like '%" + ComboBox1.SelectedItem.ToString + "%')"
        Dim comsql As String = "SELECT * FROM Course WHERE ((course_id like" + "'%" + key + "%' or course_name like" + "'%" + key + "%'or course_trainer like '%" + key + "%')and (course_start_date like '%" + keydate + "%')) order by course_start_date asc;"
        Dim adater As New MySqlCommand
        Dim dateString As String
        Dim split_start() As String

        Try
            adater.CommandText = comsql
            adater.Connection = SQL
            mysqlda.SelectCommand = adater
            mysqldr = adater.ExecuteReader

            ListView1.Items.Clear()
            While (mysqldr.Read())
                With ListView1.Items.Add(mysqldr("course_id"))
                    .subitems.add(mysqldr("course_name"))
                    split_start = Split(mysqldr("course_start_date"), "/")

                    .subItems.add(split_start(0) + "/" + split_start(1) + "/" + split_start(2))
                    .subitems.add(mysqldr("course_trainer"))
                End With
            End While

            mysqldr.Close()
            'Catch ex As MySqlException
            'MsgBox(ex.Message)
        Catch
        End Try
        SQL.Close()

    End Sub

    Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
        Showdata()
    End Sub

    Private Sub textsearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles textsearch.KeyDown
        If e.KeyCode = "13" Then
            Showdata()
        End If
    End Sub


    Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        cour_id = ListView1.SelectedItems(0).Text
    End Sub

    Private Sub Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add.Click
        Dim AddForm As addgroup2_1_2 = New addgroup2_1_2(SQL)
        AddForm.Show()
        Me.Close()
    End Sub

    Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
        cour_id = ListView1.SelectedItems(0).Text
        If cour_id = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else

            Dim NextForm As addgroup2_2 = New addgroup2_2(SQL, cour_id)
            NextForm.Show()
            Me.Close()
        End If
    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub

    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click

    End Sub

    Private Sub ButtonDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonDelete.Click
        If cour_id = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else

            Deletedata()
        End If
    End Sub

    Private Sub Deletedata()
        Dim sqlstat As String
        Dim cmd As New MySqlCommand
        Dim keyDel As String
        Dim respone As Object
        keyDel = cour_id

        respone = MsgBox("Are you sure to Delete data", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then

            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If

            '-----------------------------------Delete Couse--------------------------
            sqlstat = "DELETE FROM Course WHERE course_id ='" + keyDel + "'"
            With cmd
                .CommandText = sqlstat
                .CommandType = CommandType.Text
                .Connection = SQL
                .ExecuteNonQuery()

            End With

            SQL.Close()

            '----------------------------------Delete traning---------------------------

            If SQL.State = ConnectionState.Closed Then
                SQL.Open()
            End If

            sqlstat = "DELETE FROM Training WHERE course_id ='" + keyDel + "'"
            With cmd
                .CommandText = sqlstat
                .CommandType = CommandType.Text
                .Connection = SQL
                .ExecuteNonQuery()

            End With
            SQL.Close()

            respone = MsgBox("Delete Success", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Warning Message")

            Showdata()
        Else
            Exit Sub
        End If
    End Sub

    Private Sub btn_Edit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Edit.Click
        If cour_id = "" Then
            MsgBox("Please select your data", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Warning Message")
        Else

            Dim NextForm As EditCourse = New EditCourse(SQL, cour_id)
            NextForm.Show()
            Me.Close()
        End If
    End Sub
End Class